<?php
/*   
编辑者:写php的黄瓜兄
创建日期:2019.4.22
创建控制器/方法:提现
*/

namespace app\inter\controller;
use think\Config;
use  think\Controller;
use think\Db;

class Student extends Controller
{

    //班级概览-班级信息
    public function oursClass(){
        //班级信息
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $crt_user = $_POST['crt_user'];
        $class_id = $_POST['class_id'];
        $class_data = Db::table('app_class')->where(['s_creator_id' => $crt_user, 'class_id' => $class_id])->find();
        $class_members = Db::table('rel_class_user')->where(['class_id' => $class_data['class_id']])->select();
        $student_members_arr = [];
        foreach($class_members as  &$item){
            $student_members_arr[] = $item['user_id'];
        }
        if($class_data){
            //##班级信息
            $class_infor = [
                'class_name' => $class_data['class_name'],
                'class_members' => $class_data['class_members'],
            ];
            //及格率
            $exam_plan_list = Db::table('exam_plan')->where(['status' => 3])->order('start_date', 'desc')->select();
            $before_exam_plan = $exam_plan_list[1];
            $later_exam_plan = $exam_plan_list[0];
            if($later_exam_plan){
                $test_paper_data = Db::table('test_paper')->where(['id' => $later_exam_plan['test_paper_id']])->find();
                $score = $test_paper_data['score'];
                $sql = "SELECT count(*) as pass_sum FROM `exam_plan_student` WHERE score>=" . $score*0.6 . " AND exam_plan_id={$later_exam_plan['id']} AND student_id IN(" . join(',' , $student_members_arr) . ")";
                $pass_count = Db::query($sql);
                $class_infor['pass_rate'] =  round($pass_count[0]['pass_sum']/$class_data['class_members'], 2)*100 . "%";
                //优秀率
                $sql = "SELECT count(*) as pass_sum FROM `exam_plan_student` WHERE score>=" . $score*0.8 . " AND exam_plan_id={$later_exam_plan['id']} AND student_id IN(" . join(',' , $student_members_arr) . ")";
                $excellence_count = Db::query($sql);
                $class_infor['excellence_rate'] =  round($excellence_count[0]['pass_sum']/$class_data['class_members'], 2)*100 . "%";
                //年级排名
                $sql = "SELECT SUM(score)/COUNT(*) as ava_score,  class_id FROM exam_plan_student WHERE exam_plan_id={$later_exam_plan['id']} GROUP BY class_id ORDER BY ava_score DESC;";
                $ranking_ret = Db::query($sql);
                foreach($ranking_ret as $k => $v){
                    if($v['class_id'] == $class_id) $class_infor['ranking'] =  $k;
                }
                //上上次年级排名
                $sql = "SELECT SUM(score)/COUNT(*) as ava_score,  class_id FROM exam_plan_student WHERE exam_plan_id={$before_exam_plan['id']} GROUP BY class_id ORDER BY ava_score DESC;";
                $before_ranking_ret = Db::query($sql);
                $before_ranking = 0;
                foreach($before_ranking_ret as $k => $v){
                    if($v['class_id'] == $class_id) $before_ranking =  $k;
                }
                $class_infor['ranking_change'] =  $class_infor['ranking']-$before_ranking;  //环比
                $manager_data = Db::table('sys_user')->where(['user_id' => $_POST['crt_user']])->find();
                if($manager_data){
                    $class_infor['teacher_name'] = $manager_data['name'];
                }
                return json(['data'=>$class_infor, 'msg'=>'success', 'state'=>10000]);
            }else{
                return json(['data'=>'', 'msg'=>'暂无考试信息', 'state'=>10002]);
            }

        }else{
            return json(['data'=>'', 'msg'=>'班级不存在', 'state'=>10002]);
        }
    }


    //班级概况-学生近况
    public function studentCase(){
        //班级信息
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $exam_plan_list = Db::table('exam_plan')->where(['status' => 3])->order('start_date', 'desc')->select();
        if(!$exam_plan_list) return json(['data'=>'', 'msg'=>'未找到考试信息', 'state'=>10002]);
        $before_exam_plan = $exam_plan_list[1];
        $later_exam_plan = $exam_plan_list[0];
        //##学生近况
        //前10
        $sql = "SELECT exam_plan_student.*, app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id' ORDER BY score DESC LIMIT 10;";
        $rank_before_10_list = Db::query($sql);
        $i = 1;
        foreach($rank_before_10_list as &$item){
            $item['ranking'] = $i;
            $i++;
        }
        $student_case['rank_before_10'] = $rank_before_10_list; //前10

        //后10
        $sql = "SELECT * from  exam_plan_student WHERE  exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id';";
        $student_data_s = Db::query($sql);
        $sql = "SELECT exam_plan_student.*, app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id' ORDER BY score ASC LIMIT 10;";
        $rank_back_10_list = Db::query($sql);
        $i = count($student_data_s);
        foreach($rank_back_10_list as &$item){
            $item['ranking'] = $i;
            $i--;
        }
        $student_case['rank_back_10'] = $rank_back_10_list; //前10
        //排名进退幅度
        $sql = "SELECT exam_plan_student.* , app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id' ORDER BY score DESC;";
        $later_exam_score_list = Db::query($sql);
        $sql = "SELECT exam_plan_student.* , app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$before_exam_plan['id']} AND class_id='$class_id' ORDER BY score DESC;";
        $before_exam_score_list = Db::query($sql);
        $later_exam_score_list_deal = [];
        foreach($later_exam_score_list as $k => $v){
            $v['ranking'] = $k+1;
            $later_exam_score_list_deal[$v['student_id']] = $v;
        }
        $before_exam_score_list_deal = [];
        foreach($before_exam_score_list as $k => $v){
            $v['ranking'] = $k+1;
            $before_exam_score_list_deal[$v['student_id']] = $v;
        }
        $student_score_range = [];
        foreach($later_exam_score_list_deal as $k => $v){
            $student_score_range[] = [
                'rank_change' => $v['ranking']-$before_exam_score_list_deal[$k]['ranking'],
                'name' => $v['nick'],
                'student_id' => $v['student_id'],
                'ranking' => $v['ranking']
            ];
        }

        $last_names = array_column($student_score_range,'rank_change');
        array_multisort($last_names,SORT_DESC,$student_score_range);
        $student_case['top_range'] = $student_score_range;  //进步幅度
        $last_names = array_column($student_score_range,'rank_change');
        array_multisort($last_names,SORT_ASC,$student_score_range);
        $student_case['back_range'] = $student_score_range; //退步幅度
        return json(['data'=>$student_case, 'msg'=>'success', 'state'=>10000]);
    }

    //班级概括--近期各学科平均分对比
    public function subjectAvgData(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        //##近期各学科平均分对比
        $subject = Config::get('subject');
        $subject_avg_data = [];
        foreach($subject as $k => $v){
            $subject_exam_plan_list = Db::table('exam_plan')->where(['subject_id' => $k, 'status' => 3])->order('start_date', 'desc')->select();
            if($subject_exam_plan_list){
                //最近一次考试平均分
                $later_avg = 0;
                $sql = "SELECT AVG(score) as score_avg FROM `exam_plan_student`   WHERE  exam_plan_id={$subject_exam_plan_list[0]['id']} AND class_id='$class_id';";
                $later_suject_avg = Db::query($sql);
                if($later_suject_avg){
                    $later_avg = $later_suject_avg[0]['score_avg']?$later_suject_avg[0]['score_avg']:0;
                }
                //最近考试年级考试平均分
                $grade_grade_avg = 0;
                if(isset($subject_exam_plan_list[0])){
                    $sql = "SELECT AVG(score) as score_avg FROM `exam_plan_student`   WHERE  exam_plan_id={$subject_exam_plan_list[0]['id']};";
                    $grade_later_avg = Db::query($sql);
                    $grade_grade_avg = $grade_later_avg[0]['score_avg'];
                }
                $subject_avg_data[] = [$k, $grade_grade_avg, $later_avg];
            }else{
                $subject_avg_data[] = [$k, 0, 0];
            }
        }

        foreach($subject_avg_data as &$item){
            $item[0] = $subject[$item[0]];
        }
        $result = [];
        foreach($subject_avg_data as $k => $v){
            $result['x'][] = $v[0];
            $result['grade_subject_avg'][] = $v[1];
            $result['class_subject_avg'][] = $v[2];
        }

        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);
    }

    //班级概况-本学期各科平均分对比
    public function termScoreAvg()
    {
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        //##本学期各科平均分对比
        $exam_plan_term = Db::table('exam_plan')->where(['status' => 3])->select();
        //年级平均分
        $sql ="SELECT AVG(a.score) as score_avg, b.title  FROM `exam_plan_student` as a
                        LEFT JOIN exam_plan as b ON b.id=a.exam_plan_id
                        GROUP BY a.exam_plan_id;";
        $ret = Db::query($sql);
        $term_score_avg['grade_term_score_avg'] = $ret;  //年级平均分
        //班级平均分
        $sql = "SELECT AVG(a.score) as score_avg, b.title  FROM `exam_plan_student` as a
                      LEFT JOIN exam_plan as b ON b.id=a.exam_plan_id
                    WHERE a.class_id='$class_id'
                  GROUP BY a.exam_plan_id;";
        $ret = Db::query($sql);
        $term_score_avg['term_score_avg'] = $ret;  //班级
        return json(['data'=>$term_score_avg, 'msg'=>'success', 'state'=>10000]);
    }

    //班级概况-近期年级排名统计
    public function laterRank(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $exam_plan_list = Db::table('exam_plan')->where(['status' => 3])->order('start_date', 'desc')->select();
        if(!$exam_plan_list) return json(['data'=>'', 'msg'=>'未找到考试信息', 'state'=>10002]);
        $before_exam_plan = $exam_plan_list[1];
        $later_exam_plan = $exam_plan_list[0];
        //##近期年级排名统计
        $sql = "SELECT score, class_id  FROM `exam_plan_student`
WHERE exam_plan_id={$later_exam_plan['id']}
ORDER BY score DESC;";
        $ret = Db::query($sql);
        $later_rank = [];
        $later_rank['1_200'] = [];
        $later_rank['201_400'] = [];
        $later_rank['401_600'] = [];
        $later_rank['601_'] = [];
        foreach($ret as $k => $v){
            if($class_id == $v['class_id']){
                if($k <200) $later_rank['1_200'][] = $v;
                if($k <400 && $k>=200) $later_rank['201_400'][] = $v;
                if($k <600 && $k>=400) $later_rank['401_600'][] = $v;
                if($k>=600) $later_rank['601_'][] = $v;
            }
        }
        $later_rank['1_200'] = count($later_rank['1_200']);
        $later_rank['201_400'] = count($later_rank['201_400']);
        $later_rank['401_600'] = count($later_rank['401_600']);
        $later_rank['601_'] = count($later_rank['601_']);
        $result = [];
        $student_count = count($ret);
        foreach($later_rank as $k => $v){
            $result[] = [
                'key' =>  $k,
                'count' =>  $v,
                'student_count' =>  $student_count,
            ];
        }
        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);
    }

    //班级概况-年级排名分档对比
    public function gradeRankFD(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id ='0037c597e3f144cd9c7cd0756ea902ff';// $_POST['class_id'];
       
        //##年级排名分档对比
        $exam_plan_1 = Db::table('exam_plan')->where(['status' => 3])->select();
     
        $result = [];
        foreach($exam_plan_1 as &$item){
            $sql = "SELECT score, class_id,exam_plan_id FROM `exam_plan_student`
WHERE exam_plan_id={$item['id']}
ORDER BY score DESC;";
          
            $ret = Db::query($sql);
         
            $later_rank = [];
            $later_rank['1_200'] = [];
            $later_rank['201_400'] = [];
            $later_rank['401_600'] = [];
            $later_rank['601_'] = [];
          
            foreach($ret as $k => $v){
                 
    
                if($class_id == $v['class_id']){
                  
                    if($k['score'] <200) $later_rank['1_200'][] = $k;
            
                    if($k['score']<400 && $k['score']>=200) $later_rank['201_400'][] = $k;
                    if($k['score']<600 && $k['score']>=400) $later_rank['401_600'][] = $k;
                    if($k['score']>=600) $later_rank['601_'][] = $k;
                }
              
            }
          
           
            $later_rank['1_200'] = count($later_rank['1_200']);
            $later_rank['201_400'] = count($later_rank['201_400']);
            $later_rank['401_600'] = count($later_rank['401_600']);
            $later_rank['601_'] = count($later_rank['601_']);
            $later_rank['title'] = $item['title'];
            $result[] = $later_rank;
        }
      
        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);

    }

    //单科分析-班级信息
    public function subjectAnalyseInfor(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
        $crt_user = $_POST['crt_user']?$_POST['crt_user']:10000;

        $class_data = Db::table('app_class')->where(['s_creator_id' => $crt_user, 'class_id' => $class_id])->find();
        $class_members = Db::table('rel_class_user')->where(['class_id' => $class_data['class_id']])->select();
        if($class_data){
            //##班级信息
            $class_infor = [
                'class_name' => $class_data['class_name'],
                'class_members' => $class_data['class_members'],
            ];
            //及格率
            $exam_plan_list = Db::table('exam_plan')->where(['status' => 3, 'subject_id' => $subject_id])->order('start_date', 'desc')->select();
            if(count($exam_plan_list)<=1) return json(['data'=>'', 'msg'=>'考试数据不全', 'state'=>10002]);
            $before_exam_plan = $exam_plan_list[1];
            $later_exam_plan = $exam_plan_list[0];
            if($later_exam_plan){
                $test_paper_data = Db::table('test_paper')->where(['id' => $later_exam_plan['test_paper_id']])->find();
                $score = $test_paper_data['score'];
                $sql = "SELECT count(*) as pass_sum FROM `exam_plan_student` WHERE score>=" . $score*0.6 . " AND exam_plan_id={$later_exam_plan['id']}  AND class_id='$class_id';";
                $pass_count = Db::query($sql);
             // return json(['data'=>$sql, 'msg'=>'考试数据不全', 'state'=>10002]);
                $class_infor['pass_rate'] =  round($pass_count[0]['pass_sum']/$class_data['class_members'], 2)*100 . "%";
                //优秀率
                $sql = "SELECT count(*) as pass_sum FROM `exam_plan_student` WHERE score>=" . $score*0.8 . " AND exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id';";
                $excellence_count = Db::query($sql);
                $class_infor['excellence_rate'] =  round($excellence_count[0]['pass_sum']/$class_data['class_members'], 2)*100 . "%";
                //年级排名
                $sql = "SELECT SUM(score)/COUNT(*) as ava_score,  class_id FROM exam_plan_student WHERE exam_plan_id={$later_exam_plan['id']} GROUP BY class_id ORDER BY ava_score DESC;";
                $ranking_ret = Db::query($sql);
                foreach($ranking_ret as $k => $v){
                    if($v['class_id'] == $class_id) $class_infor['ranking'] =  $k;
                }
                //上上次年级排名
                $sql = "SELECT SUM(score)/COUNT(*) as ava_score,  class_id FROM exam_plan_student WHERE exam_plan_id={$before_exam_plan['id']} GROUP BY class_id ORDER BY ava_score DESC;";
                $before_ranking_ret = Db::query($sql);
                $before_ranking = 0;
                foreach($before_ranking_ret as $k => $v){
                    if($v['class_id'] == $class_id) $before_ranking =  $k;
                }
                $class_infor['ranking_change'] =  $class_infor['ranking']-$before_ranking;  //环比
                $sys_user = Db::table('sys_user')->where(['user_id' => $class_data['s_creator_id']])->find();
                $class_infor['manager_name'] = $sys_user['name'];
                return json(['data'=>$class_infor, 'msg'=>'success', 'state'=>10000]);
            }else{
                return json(['data'=>'', 'msg'=>'暂无考试信息', 'state'=>10002]);
            }

        }else{
            return json(['data'=>'', 'msg'=>'班级不存在', 'state'=>10002]);
        }

    }

    //单科分析-学生近况
    public function subjectAnalyseStudentLater(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
        $exam_plan_list = Db::table('exam_plan')->where(['status' => 3])->order('start_date', 'desc')->select();
        if(!$exam_plan_list) return json(['data'=>'', 'msg'=>'未找到考试信息', 'state'=>10002]);
        $before_exam_plan = $exam_plan_list[1];
        $later_exam_plan = $exam_plan_list[0];
        //##学生近况
        $sql = "SELECT a.*, b.nick FROM `exam_plan_student` AS a
 LEFT JOIN app_student as b ON  b.user_id=a.student_id
 WHERE  a.exam_plan_id={$later_exam_plan['id']} AND a.class_id='$class_id'";
        $count_data = Db::query($sql);
        $count = count($count_data);
        //前10
        $sql = "SELECT a.*, b.nick FROM `exam_plan_student` AS a
 LEFT JOIN app_student as b ON  b.user_id=a.student_id
 WHERE  a.exam_plan_id={$later_exam_plan['id']} AND a.class_id='$class_id' ORDER BY a.score DESC LIMIT 10;";
        $rank_before_10_list = Db::query($sql);
        $student_case['rank_before_10'] = $rank_before_10_list; //前10
        $i = 1;
        foreach($student_case['rank_before_10'] as &$item){
            $item['ranking'] = $i;
            $i++;
        }
        //后10
        $sql = "SELECT a.*, b.nick FROM  `exam_plan_student` AS  a
 LEFT JOIN app_student as b ON  b.user_id=a.student_id
WHERE  a.exam_plan_id={$later_exam_plan['id']} AND a.class_id='$class_id' ORDER BY a.score ASC LIMIT 10;";
        $rank_back_10_list = Db::query($sql);
        $student_case['rank_back_10'] = $rank_back_10_list; //前10
        $j = $count;
        foreach($student_case['rank_back_10'] as &$item){
            $item['ranking'] = $i;
            $i--;
        }
        //排名进退幅度
        $sql = "SELECT exam_plan_student.* , app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$later_exam_plan['id']} AND class_id='$class_id' ORDER BY score DESC;";
        $later_exam_score_list = Db::query($sql);
        $sql = "SELECT exam_plan_student.* , app_student.nick FROM `exam_plan_student` LEFT JOIN app_student ON app_student.user_id=exam_plan_student.student_id WHERE  exam_plan_id={$before_exam_plan['id']} AND class_id='$class_id' ORDER BY score DESC;";
        $before_exam_score_list = Db::query($sql);
        $later_exam_score_list_deal = [];
        foreach($later_exam_score_list as $k => $v){
            $v['ranking'] = $k+1;
            $later_exam_score_list_deal[$v['student_id']] = $v;
        }
        $before_exam_score_list_deal = [];
        foreach($before_exam_score_list as $k => $v){
            $v['ranking'] = $k+1;
            $before_exam_score_list_deal[$v['student_id']] = $v;
        }
        $student_score_range = [];
        foreach($later_exam_score_list_deal as $k => $v){
            $student_score_range[] = [
                'rank_change' => $v['ranking']-$before_exam_score_list_deal[$k]['ranking'],
                'name' => $v['nick'],
                'student_id' => $v['student_id'],
                'ranking' => $v['ranking'],
            ];
        }

        $last_names = array_column($student_score_range,'rank_change');
        array_multisort($last_names,SORT_DESC,$student_score_range);
        $student_case['top_range'] = $student_score_range;  //进步幅度
        $last_names = array_column($student_score_range,'rank_change');
        array_multisort($last_names,SORT_ASC,$student_score_range);
        $student_case['back_range'] = $student_score_range; //退步幅度
        return json(['data'=>$student_case, 'msg'=>'success', 'state'=>10000]);

    }

    //单科分析-本学期分数趋势
    public function subjectAnalyseScore(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
        $exam_plan = Db::table('exam_plan')->where(['subject_id' => $subject_id, 'status' => 3])
            ->select();
        $result = [];
        foreach($exam_plan as $k => $v){
            $sql = "SELECT MIN(score) as min_score, MAX(score) as max_score,
AVG(score) as avg_score  FROM `exam_plan_student`
WHERE class_id='$class_id' AND exam_plan_id={$v['id']};";
            $ret = Db::query($sql);
         
            if($ret){
                $result[] = [
                    'min_score' => $ret[0]['min_score'],
                    'max_score' => $ret[0]['max_score'],
                    'avg_score' => $ret[0]['avg_score'],
                    'title' => $v['title']
            ];
            }else{
                $result[] = [
                    'min_score' => 0,
                    'max_score' => 0,
                    'avg_score' => 0,
                    'title' => $v['title']
                ];
            }
        }
        $result_arr = [];
        return json(['data' => $result, 'msg' => 'success', 'state' => 10000]);
    }

    //单科分析-近期知识点掌握
    public function subjectAnalyseKnowledge(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
      
        
        $exam_plan_data = Db::table('exam_plan')->where(['status' => 3, 'subject_id' => $subject_id])->order('start_date desc')->find();
    
 //   $exam_plan_data['id']=1;
        $sql = "
SELECT d.knowledge_id,e.`name`, SUM(a.score) as score_sum, SUM(c.score) as topic_score_sum
FROM correct as a
LEFT JOIN topic_complete as b ON a.topic_complete_id=b.id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
LEFT JOIN topic_knowledge as d ON d.topic_id=c.topic_id
LEFT JOIN knowledge as e ON e.id=d.knowledge_id
LEFT JOIN exam_plan as f ON f.id=b.exam_plan_id
WHERE f.subject_id={$subject_id} AND b.class_id='$class_id' AND b.exam_plan_id={$exam_plan_data['id']}
GROUP BY d.knowledge_id";
     
   
        $result = Db::query($sql);
      
       
        foreach($result as &$item){
            $sql = "
SELECT d.knowledge_id,e.`name`, SUM(a.score) as score_sum, SUM(c.score) as topic_score_sum
FROM correct as a
LEFT JOIN topic_complete as b ON a.topic_complete_id=b.id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
LEFT JOIN topic_knowledge as d ON d.topic_id=c.topic_id
LEFT JOIN knowledge as e ON e.id=d.knowledge_id
LEFT JOIN exam_plan as f ON f.id=b.exam_plan_id
WHERE f.subject_id={$subject_id}  AND b.exam_plan_id={$exam_plan_data['id']}
AND d.knowledge_id = {$item['knowledge_id']}";
            $ret1 = Db::query($sql);
          
        
    
            $item['grade_score'] = number_format($ret1[0]['score_sum']/$item['topic_score_sum'], 2);
            $item['score_sum'] = number_format($item['score_sum']/$item['topic_score_sum'], 2);
        }
      
         
        if($result){
            return json(['data' => $result, 'msg' => 'success', 'state' => 10000]);
        }else{
            return json(['data' => '', 'msg' => 'fail', 'state' => 10002]);
        }

    }

    //单科分析-年级排名分档对比
    public function subjectAnalyseRanking(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];

        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        //##年级排名分档对比
        $exam_plan_1 = Db::table('exam_plan')->where(['status' => 3, 'subject_id' => $subject_id])->order('start_date asc')->select();
        $result = [];
        foreach($exam_plan_1 as &$item){
            $sql = "SELECT score, class_id  FROM `exam_plan_student`
WHERE exam_plan_id={$item['id']}
ORDER BY score DESC;";
            $ret = Db::query($sql);
            $later_rank = [];
            $later_rank['1_200'] = [];
            $later_rank['201_400'] = [];
            $later_rank['401_600'] = [];
            $later_rank['601_'] = [];
            foreach($ret as $k => $v){
                if($class_id == $v['class_id']){
                    if($k <200) $later_rank['1_200'][] = $v;
                    if($k <400 && $k>=200) $later_rank['201_400'][] = $v;
                    if($k <600 && $k>=400) $later_rank['401_600'][] = $v;
                    if($k>=600) $later_rank['601_'][] = $v;
                }
            }
            $later_rank['1_200'] = count($later_rank['1_200']);
            $later_rank['201_400'] = count($later_rank['201_400']);
            $later_rank['401_600'] = count($later_rank['401_600']);
            $later_rank['601_'] = count($later_rank['601_']);
            $later_rank['title'] = $item['title'];
            $result[] = $later_rank;
        }
        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);
    }

    //单科分析-偏科学生分析-弱科学生top10
    public function subjectAnalyseBadTop10(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
        //单科排名
        $exam_plan_data = Db::table('exam_plan')->where(['status' => 3, 'subject_id' => $subject_id])->order('start_date asc')->find();
        if(!$exam_plan_data) return json(['data'=>'', 'msg'=>'未查询到单科排名数据', 'state'=>10002]);
        $sql = "SELECT a.student_id,a.score, b.nick  FROM `exam_plan_student` AS a
LEFT  JOIN  `app_student` AS  b ON  b.user_id=a.student_id
WHERE exam_plan_id={$exam_plan_data['id']} AND  class_id='$class_id'
ORDER BY score DESC;";
        $dan_subject_ranking_list = Db::query($sql);
        $student_sum = count($dan_subject_ranking_list);
        $dan_subject_ranking_ret = [];
        foreach($dan_subject_ranking_list as $k => $v){
            $v['ranking'] = $k+1;
            $dan_subject_ranking_ret[] = $v;
        }
        $result = array_slice($dan_subject_ranking_ret, -11, -1);
        $result = array_reverse($result);

        $student_arr = [];
        foreach($result as $item){
            $student_arr[] = $item['student_id'];
        }
        $student_sting = join(',', $student_arr);
        //综合排名
        $subject_list = Config::get('subject');
        $ex_plan_id_arr = [];
        foreach($subject_list as $k => $v){
            $ret1 = Db::table('exam_plan')->where(['subject_id' => $k])
                ->order('start_date','desc')->find();
            if($ret1)  $ex_plan_id_arr[] = $ret1['id'];

        }
        $ex_plan_id_string = join(',', $ex_plan_id_arr);
        $sql = "SELECT SUM(score) AS score_sum , student_id FROM `exam_plan_student`
WHERE exam_plan_id in({$ex_plan_id_string}) AND class_id='$class_id' AND student_id IN ({$student_sting})
GROUP BY student_id ORDER BY score_sum DESC;";
        $ret2 = Db::query($sql);
        $i = 1;
        $zh_rank_data = [];
        foreach($ret2 as &$item){
            $item['zh_ranking'] = $i;
            $i++;
            $zh_rank_data[$item['student_id']] = $item;
        }

        foreach($result as &$item){
            $item['zh_ranking'] = $zh_rank_data[$item['student_id']]['zh_ranking'];
            $item['differ'] = abs($item['ranking']-$item['zh_ranking']);
        }


        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);
    }

    //单科分析-偏科学生分析-强科学生top10
    public function subjectAnalyseGoodTop10(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['subject_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $class_id = $_POST['class_id'];
        $subject_id = $_POST['subject_id'];
        //单科排名
        $exam_plan_data = Db::table('exam_plan')->where(['status' => 3, 'subject_id' => $subject_id])->order('start_date asc')->find();
        if(!$exam_plan_data) return json(['data'=>'', 'msg'=>'未查询到单科排名数据', 'state'=>10002]);
        $sql = "SELECT a.student_id,a.score, b.nick  FROM `exam_plan_student` AS a
LEFT  JOIN  `app_student` AS  b ON  b.user_id=a.student_id
WHERE exam_plan_id={$exam_plan_data['id']} AND  class_id='$class_id'
ORDER BY score DESC;";
        $dan_subject_ranking_list = Db::query($sql);
        $student_sum = count($dan_subject_ranking_list);
        $dan_subject_ranking_ret = [];
        foreach($dan_subject_ranking_list as $k => $v){
            $v['ranking'] = $k+1;
            $dan_subject_ranking_ret[] = $v;
        }
        $result = array_slice($dan_subject_ranking_ret, 0, 10);

        $student_arr = [];
        foreach($result as $item){
            $student_arr[] = $item['student_id'];
        }
        $student_sting = join(',', $student_arr);
        //综合排名
        $subject_list = Config::get('subject');
        $ex_plan_id_arr = [];
        foreach($subject_list as $k => $v){
            $ret1 = Db::table('exam_plan')->where(['subject_id' => $k])
                ->order('start_date','desc')->find();
            if($ret1)  $ex_plan_id_arr[] = $ret1['id'];

        }
        $ex_plan_id_string = join(',', $ex_plan_id_arr);
        $sql = "SELECT SUM(score) AS score_sum , student_id FROM `exam_plan_student`
WHERE exam_plan_id in({$ex_plan_id_string}) AND class_id='$class_id' AND student_id IN ({$student_sting})
GROUP BY student_id ORDER BY score_sum DESC;";
        $ret2 = Db::query($sql);
        $i = 1;
        $zh_rank_data = [];
        foreach($ret2 as &$item){
            $item['zh_ranking'] = $i;
            $i++;
            $zh_rank_data[$item['student_id']] = $item;
        }

        foreach($result as &$item){
            $item['zh_ranking'] = $zh_rank_data[$item['student_id']]['zh_ranking'];
            $item['differ'] = abs($item['ranking']-$item['zh_ranking']);
        }

        return json(['data'=>$result, 'msg'=>'success', 'state'=>10000]);
    }



    public function testPaperList(){
        $subject = Config::get('subject');
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $where = " a.crt_user=10000 and a.class_id='{$_POST['class_id']}'";
        if(isset($_POST['keyword']) && !empty($_POST['keyword'])) $where .= " and b.title like '%{$_POST['keyword']}%'";
        if(isset($_POST['start_date']) && !empty($_POST['start_date'])) $where .=" and start_date='{$_POST['start_date']}'";
        $sql = "SELECT a.*,a.id as exam_plan_class_id, b.subject_id, b.start_date, b.title, c.class_name FROM `exam_plan_class` AS a
LEFT JOIN  exam_plan AS b ON a.exam_plan_id=b.id
LEFT JOIN app_class  AS c ON c.class_id=a.class_id
WHERE $where";
        $list = Db::query($sql);
        foreach($list as &$item){
            $item['subject_id_label'] = isset($subject[$item['subject_id']])?$subject[$item['subject_id']]:'';
        }
        return json(['data'=>$list, 'msg'=>'success', 'state'=>10001]);
    }

    //试卷分析--试卷信息
    public function testPaperInfor(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $subject = Config::get('subject');
        $degree = Config::get('degree');
        $scope = Config::get('scope');
        $sql = "SELECT a.*, b.subject_id, b.start_date, b.title,b.num,
 b.start_time,b.end_time, c.class_name, e.name as teacher_name,
d.degree, d.scope, d.title as test_paper_title
 FROM `exam_plan_class` AS a
LEFT JOIN  exam_plan AS b ON a.exam_plan_id=b.id
LEFT JOIN app_class  AS c ON c.class_id=a.class_id
LEFT JOIN test_paper  AS d ON d.id=b.test_paper_id
LEFT  JOIN sys_user AS e ON  e.user_id=d.crt_user
WHERE a.id={$_POST['exam_plan_class_id']}";
        $data = Db::query($sql);
//        var_dump($data);exit();
        foreach($data as &$item){
            $item['scope_label'] = $scope[$item['scope']];
            $item['subject_id_label'] = $subject[$item['subject_id']];
            $item['degree_label'] = $degree[$item['degree']];
            $item['exam_time'] = $item['start_date']." ".date('H:I', strtotime($item['start_time']))."~".date('H:I', strtotime($item['end_time']));
            $item['exam_time_length'] = floor((strtotime($item['end_time'])-strtotime($item['start_time']))/60);
        }
        return json(['data'=>$data[0], 'msg'=>'success', 'state'=>10000]);
    }

    //试卷分析--题型分数占比
    public function testPaperTopicScore(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
     
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        $test_paper_data = Db::table('test_paper')->where(['id' => $exam_plan_data['test_paper_id']])->find();
        $sql = "SELECT SUM(a.score) as score_sum, b.type FROM `test_paper_topic` as a
LEFT JOIN topic as b ON b.id=a.topic_id
WHERE a.test_paper_id={$test_paper_data['id']}
GROUP BY b.type;";
        $topic_list = Db::query($sql);
        $sql = "SELECT SUM(a.score) as score_sum FROM `test_paper_topic` as a
LEFT JOIN topic as b ON b.id=a.topic_id
WHERE a.test_paper_id={$exam_plan_data['test_paper_id']}";
        $score_total = Db::query($sql);
        $score_sum =$score_total[0]['score_sum'];
        $topic_type = Config::get('topic_type');
        foreach($topic_list as &$item){
            $item['score_rate'] = number_format($item['score_sum']/$score_sum, 2)*100 . "%";
            $item['type_name'] = $topic_type[$item['type']];
            $item['score_total'] = $score_sum;
        }
        return json(['data' => $topic_list, 'score_sum' => $score_sum, 'state' => 10000, 'msg' => 'success']);
    }
  
    //试卷分析--丢分趋势图
    public function testPaperLoseScore(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['type'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
        $class_id = $_POST['class_id'];
        $type = $_POST['type'];
       
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        $test_paper_data = Db::table('test_paper')->where(['id' => $exam_plan_data['test_paper_id']])->find();
        $sql = "SELECT a.score, b.type, b.id FROM `test_paper_topic` as a
LEFT JOIN topic as b ON b.id=a.topic_id
WHERE a.test_paper_id={$test_paper_data['id']} AND  b.type={$type}
 ORDER  BY  b.id ASC
";

        $topic_list = Db::query($sql);
  
      
        $result = [];
         $i=1;
        foreach($topic_list as &$item){
        
            $sql = "SELECT sum(a.score) AS score_sum, count(*) as join_count FROM `correct` AS a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
WHERE b.topic_id={$item['id']} AND  b.class_id='{$class_id}';";
            $ret1 = Db::query($sql);
      
             
            if($ret1[0]['score_sum']){
                $item['lose_points_rate'] = $ret1[0]['score_sum']/($ret1[0]['join_count']*$item['score']);
                $item['topic_name'] = "第{$i}题";
                $result['topic_name'][] = $item['topic_name'];
                $result['lose_points_rate'][] = $item['lose_points_rate'];
            }
          $i++;

        }
      
        return json([ 'data' => $result, 'state' => 10000, 'msg' => 'success']);
    }

    //试卷分析--分数段人数对比
    public function testPaperScoreD(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        $result['x'] = ['0-40','40-60','60-80','80-100'];
        $result['y'] = [0,0,0,0];
        $data = Db::table('exam_plan_student')->where([
            'class_id' => $_POST['class_id'],
            'exam_plan_id' => $exam_plan_data['id'],
        ])->select();
        foreach($data as &$item){
            if($item['score']>=0 && $item['score']<=40) $result['y'][0]++;
            if($item['score']>40 && $item['score']<=60) $result['y'][1]++;
            if($item['score']>60 && $item['score']<=80) $result['y'][2]++;
            if($item['score']>80 && $item['score']<=100) $result['y'][3]++;
        }
        return json(['data' => $result, 'msg' => 'success', 'state' => 10000]);

    }

    //试卷分析--知识点占比
    public function testPaperKnowLedge(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['type'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
        $class_id = $_POST['class_id'];
        $type = $_POST['type'];
  
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        
        $test_paper_data = Db::table('test_paper')->where(['id' => $exam_plan_data['test_paper_id']])->find();
      
        $sql = "SELECT COUNT(*) AS total_k,  f.`name` , c.type, d.knowledge_id FROM `correct` AS a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN topic AS c ON c.id=b.topic_id
LEFT JOIN test_paper_topic AS e ON e.topic_id=c.id
LEFT JOIN topic_knowledge as d ON d.topic_id=c.id
LEFT JOIN knowledge as f ON f.id=d.knowledge_id
WHERE b.exam_plan_id={$exam_class_plan_data['exam_plan_id']} AND b.class_id='{$class_id}'
GROUP BY d.knowledge_id;
";
        $topic_list = Db::query($sql);
  
        $result = [];
        foreach($topic_list as &$item){
            $result['knowledge_name'][] = $item['name'];
            $result['topic_count'][] = $item['total_k'];
            $sql = "SELECT COUNT(*) AS total_k,  f.`name` ,  c.type, d.knowledge_id FROM `correct` AS a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN topic AS c ON c.id=b.topic_id
LEFT JOIN test_paper_topic AS e ON e.topic_id=c.id
LEFT JOIN topic_knowledge as d ON d.topic_id=c.id
LEFT JOIN knowledge as f ON f.id=d.knowledge_id
WHERE b.exam_plan_id=1  AND b.class_id='{$class_id}' AND  c.type={$type}
  AND d.knowledge_id={$item['knowledge_id']}
GROUP BY d.knowledge_id;
";
            $ret1 = Db::query($sql);
            if($ret1){
                $result['count'][] = $ret1[0]['total_k'];
            }else{
                $result['count'][] = 0;
            }
        }

        return json(['data' => $result, 'msg' => 'success', 'state' => 10000]);

    }
    //试卷分析--高低分前十排名名单
    public function testPaperHighLow(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
        $class_id = $_POST['class_id'];
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        $sql = "SELECT a.*, b.nick, c.class_name FROM `exam_plan_student` AS a
LEFT JOIN app_student AS b ON b.user_id=a.student_id
LEFT  JOIN app_class as c ON  c.class_id=a.class_id
WHERE a.class_id='{$class_id}' AND a.exam_plan_id={$exam_plan_data['id']}
ORDER BY a.score DESC;";
        $ret = Db::query($sql);
        $i = 1;
        $data = [];
        foreach($ret as &$item){
            //计算错题率
            $sql = "SELECT * FROM `correct` as a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
WHERE b.student_id={$item['student_id']} AND b.exam_plan_id={$item['exam_plan_id']}
;";
            $count_data = Db::query($sql);
            $count = count($count_data);
            $sql = "SELECT * FROM `correct` as a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
WHERE b.student_id={$item['student_id']} AND b.exam_plan_id={$item['exam_plan_id']}
and a.score!=c.score;";
            $error_ret =  Db::query($sql);
            $error_count = count($error_ret);
            $data[] = [
                'nick' => $item['nick'],
                'ranking' => $i,
                'score' => $item['score'],
                'class_name' => $item['class_name'],
                'error_rate' => $count?number_format($error_count/$count, 2)*100 . "%" : '0',
            ];
            $i++;
        }

        $result['ranking_before_10'] = array_slice($data, 0,10);
        $result['ranking_back_10'] = array_slice($data, -11,-1);
        $result['ranking_back_10'] = array_reverse($result['ranking_back_10']);
        return json(['data' => $result, 'msg' => 'success', 'state' => 10000]);
    }
    //试卷分析--高低分进退幅度名单
    public function testPaperRankingRange(){
        if(!isset($_POST['exam_plan_class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $exam_plan_class_id = $_POST['exam_plan_class_id'];
        $class_id = $_POST['class_id'];
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();

        $exam_class_plan_list = Db::table('exam_plan_class')->where([
            'class_id' => $class_id,
        ])->select();
        if(count($exam_class_plan_list)>=2){
            $before_list = self::getRankingBeforeData($exam_class_plan_list[1]['id'], $class_id);
        }else{
            return json(['data'=>'', 'msg'=>'未找到上次考试信息', 'state'=>10002]);
        }


        $sql = "SELECT a.*, b.nick, c.class_name FROM `exam_plan_student` AS a
LEFT JOIN app_student AS b ON b.user_id=a.student_id
LEFT  JOIN app_class as c ON  c.class_id=a.class_id
WHERE a.class_id='{$class_id}' AND a.exam_plan_id={$exam_plan_data['id']}
ORDER BY a.score DESC;";
        $ret = Db::query($sql);
        $i = 1;
        $data = [];
        foreach($ret as &$item){
            //计算错题率
            $sql = "SELECT * FROM `correct` as a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
WHERE b.student_id={$item['student_id']} AND b.exam_plan_id={$item['exam_plan_id']}
;";
            $count_data = Db::query($sql);
            $count = count($count_data);
            $sql = "SELECT * FROM `correct` as a
LEFT JOIN topic_complete as b ON b.id=a.topic_complete_id
LEFT JOIN test_paper_topic as c ON c.topic_id=b.topic_id
WHERE b.student_id={$item['student_id']} AND b.exam_plan_id={$item['exam_plan_id']}
and a.score!=c.score;";
            $error_ret =  Db::query($sql);
            $error_count = count($error_ret);
            $tmp_rank = isset($before_list[$item['student_id']])?$before_list[$item['student_id']]:0;
            $data[] = [
                'nick' => $item['nick'],
                'ranking' => $i,
                'score' => $item['score'],
                'class_name' => $item['class_name'],
                'error_rate' => $count?number_format($error_count/$count, 2)*100 . "%":'0',
                'rank_change' => $i-$tmp_rank
            ];
            $i++;
        }


        $last_names = array_column($data,'rank_change');
        array_multisort($last_names,SORT_DESC,$data);
        $student_case['top_range'] = array_slice($data, 0,10);  //进步幅度
        $last_names = array_column($data,'rank_change');
        array_multisort($last_names,SORT_ASC,$data);
        $student_case['back_range'] = array_slice($data, 0,10);; //退步幅度

        return json(['data' => $student_case, 'msg' => 'success', 'state' => 10000]);

    }

    private function getRankingBeforeData($exam_plan_class_id,$class_id){
        $exam_class_plan_data = Db::table('exam_plan_class')->where(['id' => $exam_plan_class_id])->find();
        $exam_plan_data = Db::table('exam_plan')->where(['id' => $exam_class_plan_data['exam_plan_id']])
            ->find();
        $sql = "SELECT a.*, b.nick, c.class_name FROM `exam_plan_student` AS a
LEFT JOIN app_student AS b ON b.user_id=a.student_id
LEFT  JOIN app_class as c ON  c.class_id=a.class_id
WHERE a.class_id='{$class_id}' AND a.exam_plan_id={$exam_plan_data['id']}
ORDER BY a.score DESC;";
        $ret = Db::query($sql);
        $i = 1;
        $data = [];
        foreach($ret as &$item){

            $data[$item['student_id']] = $i;
            $i++;
        }
       return $data;
    }

    //学生详情--学生列表
    public function studentDetailList(){
        if(!isset($_POST['page'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['type'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $page = $_POST['page']?$_POST['page']:1;
        $class_id = $_POST['class_id'];
        $keyword = $_POST['keyword'];
        $type = $_POST['type'];//排序:1 姓名 2排名 3学号id

        $sql = "SELECT * FROM `exam_plan_class` AS a
LEFT JOIN exam_plan as b ON b.id=a.exam_plan_id
where a.class_id='{$class_id}' ORDER BY start_date DESC;";
        $exam_plan_list = Db::query($sql);
        if(!$exam_plan_list) return json(['data'=>'', 'msg'=>'查询失败', 'state'=>10002]);
        $exam_plan_id = $exam_plan_list[0]['id'];
        $page_count = Config::get('page_count');
        $limit = ($page-1)*$page_count . "," . $page_count*$page;
        if($type ==1 ){
            $order = "b.nick";
        }elseif($type == 2){
            $order = "a.score";
        }elseif($type == 3){
            $order = "b.user_id";
        }else{
            $order = "b.nick";
        }
        $sql = "SELECT a.*, b.nick FROM `rel_class_user` AS a
LEFT JOIN app_student as b ON b.user_id=a.user_id
WHERE  b.nick like '%{$keyword}%' AND  a.class_id = '{$class_id}'
ORDER BY $order  DESC
LIMIT $limit
;";
        $result = Db::query($sql);
//        var_dump($result);exit();
        $i = 1;
        foreach($result as &$item){
            $item['ranking'] = $i;
            $i++;
        }

        $class_data = Db::table('app_class')->where(['class_id' => $class_id])->find();


        return json(['data' => $result,'grade_id' => $class_data['grade_level_id'], 'page' => $page, 'page_num' => ceil(count($exam_plan_list)/Config::get('page_count')), 'state' => 10000]);

    }




    //学生详情--学生信息
    public function studentDetail(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['student_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        $sql = "SELECT * FROM exam_plan_class
where class_id='{$class_id}' ORDER BY create_time DESC;";
        $ret = Db::query($sql);

        $sql ="SELECT * FROM exam_plan_student WHERE class_id='{$class_id}'
AND exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
        $ret1 = Db::query($sql);
        $data = [];
        $i = 1;
        foreach($ret1 as $k => $v){
            if($student_id == $v['student_id']){
                $data['score'] = $v['score'];
                $data['class_ranking'] = $i;
            }
            $i++;
        }

        $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
        $ret2 = Db::query($sql);
        $i = 1;
        foreach($ret2 as $k => $v){
            if($student_id == $v['student_id']){
                $data['grade_ranking'] = $i;
            }
            $i++;
        }

        $sql ="
SELECT sum(a.score) as score_sum FROM test_paper_topic AS a
LEFT JOIN test_paper as b ON b.id=a.test_paper_id
LEFT JOIN exam_plan as c ON c.test_paper_id=b.id
WHERE c.id={$ret[0]['exam_plan_id']}";
        $ret2 = Db::query($sql);
        $data['rate'] = number_format($data['score']/$ret2[0]['score_sum'],2)*100 . "%";


        //上次考试情况
        if($ret[1]['exam_plan_id']){
            $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[1]['exam_plan_id']} ORDER BY score DESC";
            $ret2 = Db::query($sql);
            $i = 1;
            $data1['grade_ranking'] = 0;
            foreach($ret2 as $k => $v){
                if($student_id == $v['student_id']){
                    $data1['grade_ranking'] = $i;
                }
                $i++;
            }
            $data['assess'] = $data['grade_ranking']-$data1['grade_ranking'];
        }else{
            $data['assess'] = $data['grade_ranking'];
        }



        return json(['data'=>$data, 'msg'=>'success', 'state'=>10000]);
    }


    //学生详情--考试成绩单
    public function studentDetailReport(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['student_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        $sql = "SELECT a.*, b.title FROM exam_plan_class as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}' ORDER BY a.create_time DESC;";
        $ret = Db::query($sql);
        $result = [];
        foreach($ret as $k10 => $v10){
            $data = [];
            //班级排名
            $sql ="SELECT * FROM exam_plan_student WHERE class_id='{$class_id}'
AND exam_plan_id={$ret[$k10]['exam_plan_id']} ORDER BY score DESC";
            $ret1 = Db::query($sql);
            if(!$ret1) continue;
            $i = 1;
            foreach($ret1 as $k => $v){
                if($student_id == $v['student_id']){
                    $data['title'] = $v10['title'];
                    $data['score'] = $v['score'];
                    $data['class_ranking'] = $i;
                }
                $i++;
            }
            //班级排名浮动
            if(isset($ret[$k10-1]['exam_plan_id'])){
                $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[$k10-1]['exam_plan_id']} ORDER BY score DESC";
                $ret2 = Db::query($sql);
                if(!$ret2) continue;
                $i = 1;
                $data1['class_ranking'] = 0;
                foreach($ret2 as $k => $v){
                    if($student_id == $v['student_id']){
                        $data1['class_ranking'] = $i;
                    }
                    $i++;
                }
                $data['class_assess'] = $data['class_ranking']-$data1['class_ranking'];
            }else{
                $data['class_assess'] = $data['class_ranking'];
            }

            //年级排名
            $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[$k10]['exam_plan_id']} ORDER BY score DESC";
            $ret2 = Db::query($sql);
            if(!$ret2) continue;
            $i = 1;
            foreach($ret2 as $k => $v){
                if($student_id == $v['student_id']){
                    $data['grade_ranking'] = $i;
                }
                $i++;
            }
            //年级上次考试情况
            if(isset($ret[$k10-1]['exam_plan_id'])){
                $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[$k10-1]['exam_plan_id']} ORDER BY score DESC";
                $ret2 = Db::query($sql);
                if(!$ret2) continue;
                $i = 1;
                $data1['grade_ranking'] = 0;
                foreach($ret2 as $k => $v){
                    if($student_id == $v['student_id']){
                        $data1['grade_ranking'] = $i;
                    }
                    $i++;
                }
                $data['grade_assess'] = $data['grade_ranking']-$data1['grade_ranking'];
//                $data['grade_assess'] = $data1['grade_ranking'];
            }else{
                $data['grade_assess'] = $data['grade_ranking'];
            }

            if($data){
                $data['exam_plan_id'] = $v10['exam_plan_id'];
                $result[] = $data;
            }
        }
        return json(['data' => $result,  'state' => 10000]);
    }

    //学生详情--本学期分数趋势图
    public function studentDetailScore(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['student_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        //个人考试成绩
        $sql = "SELECT a.*, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}' AND  a.student_id={$student_id};";
        $ret1 = Db::query($sql);
        //班级平均分
        $sql = "SELECT avg(a.score) as class_avg_score, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}'
GROUP  BY  a.exam_plan_id;";
        $ret2 = Db::query($sql);
        //年级平均分
        $sql = "SELECT avg(a.score) as class_avg_score, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
GROUP  BY  a.exam_plan_id;";
        $ret3 = Db::query($sql);
        $result = [];
        foreach($ret1 as &$item){
            $result['student'][] = $item['score'];
            $result['title'][] = $item['title'];
        }
        foreach($ret2 as &$item){
            $result['class'][] = $item['class_avg_score'];
        }
        foreach($ret3 as &$item){
            $result['grade'][] = $item['class_avg_score'];
        }
        return json(['data' => $result,  'state' => 10000]);
    }

    //学生详情--近期各科成绩平均分对比
    public function studentDetailAvgScore(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['student_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        $subject = Config::get('subject');
        $result = [];
        foreach($subject as $k => $v){
            //个人
            $sql = "SELECT a.*, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
LEFT JOIN exam_plan_class as c ON c.exam_plan_id=b.id
where b.subject_id='$k' AND  a.student_id={$student_id}
ORDER BY c.create_time DESC;;";
            $ret1 = Db::query($sql);
            if(!$ret1){
                continue;
            }
            $sql = "SELECT a.*, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where b.subject_id='{$k}' AND  a.student_id={$student_id} AND a.exam_plan_id={$ret1[0]['exam_plan_id']};";
            $ret1 = Db::query($sql);
            $result['student'][] = $ret1[0]['score'];
            $result['subject_name'][] = $v;
            //班级
            $sql = "SELECT avg(a.score) as score_avg, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where b.subject_id='{$k}' AND  class_id='{$class_id}' AND a.exam_plan_id={$ret1[0]['exam_plan_id']};";
            $ret2 = Db::query($sql);
            $result['class'][] = $ret2[0]['score_avg'];
            //年级
            $sql = "SELECT avg(a.score) as score_avg, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where b.subject_id='{$k}' AND a.exam_plan_id={$ret1[0]['exam_plan_id']};";
            $ret3 = Db::query($sql);
            $result['grade'][] = $ret3[0]['score_avg'];
        }
        return json(['data' => $result,  'state' => 10000]);
    }

    //学生详情--本学期排名趋势图
    public function studentDetailRanking(){
        if(!isset($_POST['class_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        if(!isset($_POST['student_id'])) return json(['data'=>'', 'msg'=>'参数不全', 'state'=>10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        $sql = "SELECT a.*, b.title FROM exam_plan_class as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}' ORDER BY a.create_time DESC;";
        $ret = Db::query($sql);
        $result = [];
        foreach($ret as &$item){
            $data = '';
            //班级排名
            $sql ="SELECT * FROM exam_plan_student WHERE class_id='{$class_id}'
AND exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
            $ret1 = Db::query($sql);
            if(!$ret1) continue;
            $i = 1;
            foreach($ret1 as $k => $v){
                if($student_id == $v['student_id']){
                    $data['title'] = $item['title'];
                    $data['score'] = $v['score'];
                    $data['class_ranking'] = $i;
                }
                $i++;
            }

            //年级排名
            $sql ="SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
            $ret2 = Db::query($sql);
            $i = 1;
            foreach($ret2 as $k => $v){
                if($student_id == $v['student_id']){
                    $data['grade_ranking'] = $i;
                }
                $i++;
            }
            $result[] = $data;
        }
        $ret3 = [];
        foreach($result as $item){
            if(!isset($item['title'])) continue;
            $ret3['title'][] = $item['title'];
            $ret3['class_ranking'][] = $item['class_ranking'];
            $ret3['grade_ranking'][] = $item['grade_ranking'];
        }

        $data1 =  $ret3;

        //######学生详情--本学期分数趋势图
        //个人考试成绩
        $sql = "SELECT a.*, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}' AND  a.student_id={$student_id};";
        $ret1 = Db::query($sql);
        //班级平均分
        $sql = "SELECT avg(a.score) as class_avg_score, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}'
GROUP  BY  a.exam_plan_id;";
        $ret2 = Db::query($sql);
        //年级平均分
        $sql = "SELECT avg(a.score) as class_avg_score, b.title FROM exam_plan_student as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
GROUP  BY  a.exam_plan_id;";
        $ret3 = Db::query($sql);
        $result = [];
        foreach($ret1 as &$item){
            $result['student'][] = $item['score'];
            $result['title'][] = $item['title'];
        }
        foreach($ret2 as &$item){
            $result['class'][] = $item['class_avg_score'];
        }
        foreach($ret3 as &$item){
            $result['grade'][] = $item['class_avg_score'];
        }

        $data2 = $result;

        return json(['data1' => $data1, 'data2' => $data2,  'state' => 10000]);
    }

    //学生详情--优劣势科目top3列表
    public function studentDetailSubTop3()
    {
        if (!isset($_POST['class_id'])) return json(['data' => '', 'msg' => '参数不全', 'state' => 10002]);
        if (!isset($_POST['student_id'])) return json(['data' => '', 'msg' => '参数不全', 'state' => 10002]);
        if (!isset($_POST['grade_id'])) return json(['data' => '', 'msg' => '参数不全', 'state' => 10002]);
        $student_id = $_POST['student_id'];
        $class_id = $_POST['class_id'];
        $grade_id = $_POST['grade_id'];
        $subject = Config::get('subject');
     
        $result = [];
        $class_student_num = Db::table('rel_class_user')->where([
            'class_id' => $class_id
        ])->count('*');
        $grade_student_num = Db::table('rel_class_user')->where([
            'grade_id' => $grade_id
        ])->count('*');
        foreach ($subject as $k1 => $v1) {

            $sql = "SELECT a.*, b.title FROM exam_plan_class as a
LEFT JOIN exam_plan AS  b ON  b.id=a.exam_plan_id
where a.class_id='{$class_id}' AND  b.subject_id = {$k1} ORDER BY a.create_time DESC;";
            $ret = Db::query($sql);
            if (!$ret) continue;
//            var_dump($ret[0]['exam_plan_id']);exit();
     
            foreach ($ret as &$item) {
                $data = '';
                //班级排名
                $sql = "SELECT * FROM exam_plan_student WHERE class_id='{$class_id}'
AND exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
                $ret1 = Db::query($sql);
          
                $i = 1;
                foreach ($ret1 as $k => $v) {
                    if ($student_id == $v['student_id']) {
                        $data['subject_name'] = $v1;
                        $data['title'] = $item['title'];
                        $data['score'] = $v['score'];
                        $data['class_ranking'] = $i;
                        $data['class_chao_num'] = $class_student_num - $i;
                    }
                    $i++;
                }


                //年级排名
                $sql = "SELECT * FROM exam_plan_student WHERE
 exam_plan_id={$ret[0]['exam_plan_id']} ORDER BY score DESC";
                $ret2 = Db::query($sql);
             
                $i = 1;
                foreach ($ret2 as $k => $v) {
                    if ($student_id == $v['student_id']) {
                        $data['grade_ranking'] = $i;
                        $data['grade_chao_num'] = $grade_student_num - $i;
                    }
                    $i++;
                }


                if ($data) {
                    $data['exam_plan_id'] = $item['exam_plan_id'];
                    $result[] = $data;
                }
            }

        }
 
        $last_names = array_column($result,'class_ranking');
        array_multisort($last_names,SORT_DESC,$result);
      
      
        $student_case['back3'] = $result;  //进步幅度
        $student_case['back3'] = [
            $result[0],
            $result[1],
            $result[2],
            $result[3],
            $result[4],
        ];
        $last_names = array_column($result,'class_ranking');
        array_multisort($last_names,SORT_ASC,$result);
        $student_case['top3'] = $result; //退步幅度
        $student_case['top3'] = [
            $result[0],
            $result[1],
            $result[2],
            $result[3],
            $result[4],
        ];
        $student_case['total'] = $class_student_num;
        return json(['data' => $student_case, 'state' => 10000]);
    }


    //试卷分析
    public function testPaperAnalyse(){
        $list = Db::table('test_paper_topic')->where(['test_paper_id' => 11])->select();
        foreach($list as $k => $v){
            $stu = Db::table('rel_class_user')->select();
            foreach($stu as &$item){
                $data = [
                    'student_id' => $item['user_id'],
                    'exam_plan_id' => 11,
                    'topic_id' => $v['topic_id'],
                    'crt_time' => time(),
                    'class_id' =>$item['class_id']
                ];
               $ret =  Db::table('topic_complete')->insert($data, true, true);
                $c_data = [
                    'topic_complete_id' => $ret,
                    'score' => $ret,
                    'crt_user' => 10000,
                    'crt_time' => time(),
                ];
                Db::table('correct')->insert($c_data, true, true);
            }

        }
        echo "success!!";
        exit();

        $list = Db::table('topic')->select();
        /*foreach($list as $k => $v){
            $data = [
                'topic_id' => $v['id'],
                'knowledge_id' => $v['id'],
            ];
            Db::table('topic_knowledge')->insert($data);
            $k_data = [
                'name' => '知识点'.$v['id'],
                'subject_id' => $v['subject_id'],
                'grade_id' => $v['grade_id'],
                'crt_user' => 10000,
                'crt_time' => time()
            ];
            Db::table('knowledge')->insert($k_data);
        }*/



        foreach($list as $k => $v){
            $data = [
                'test_paper_id' => 9,
                'topic_id' => $v['id'],
                'score' => 10,
                'crt_time' => time(),
            ];
            Db::table('test_paper_topic')->insert($data);
        }

        echo 'success!!!';
    }

    //获取题型
    public function getTopicType(){
        $data = Config::get('topic_type');
        return json($data);
    }
       
  
  
}